using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.ComponentModel;
using ErrorDumper.Writing;

namespace ErrorDumper.Providing {
    public class SqlExceptionProvider : IExceptionProvider {
        public SqlExceptionProvider(string connectionString) {
            if (string.IsNullOrEmpty(_connectionString = connectionString))
                throw new ArgumentException("is null or empty", "connectionString");
        }

        readonly string _connectionString;

        public int GetCount(ExceptionQuery query) {
            if (query == null)
                throw new ArgumentNullException("query");

            var sql = new StringBuilder("select count(distinct c.[Handle])\r\n");
            var parameters = new List<SqlParameter>();

            RenderFromSection(query, sql, parameters);

            using (var conn = new SqlConnection(_connectionString)) {
                conn.Open();
                using (var cmd = new SqlCommand(sql.ToString(), conn)) {
                    foreach (var param in parameters)
                        cmd.Parameters.Add(param);

                    using (var reader = cmd.ExecuteReader())
                        if (reader.Read())
                            return Convert.ToInt32(reader[0]);
                }
            }

            return 0;
        }

        public ICollection<ExceptionHeader> GetHeaders(ExceptionQuery query) {
            if (query == null)
                throw new ArgumentNullException("query");

            var sort = query.Sort == ListSortDirection.Ascending ? "asc" : "desc";
            var parameters = new List<SqlParameter>();

            var sql = new StringBuilder("select [ExceptionProperty].[Handle], [ExceptionProperty].[Name], [ExceptionProperty].[Value] from ( select [Handle], row_number() over (order by [Handle] ");
            sql.Append(sort);
            sql.AppendLine(") as RowNumber from ( select distinct c.[Handle]");
            RenderFromSection(query, sql, parameters);
            sql.Append(") as filtered ) as numbered join [ExceptionProperty] on numbered.[Handle] = [ExceptionProperty].[Handle] where numbered.RowNumber between ");
            sql.Append(query.Skip + 1);
            sql.Append(" and ");
            sql.Append(query.Skip + query.Take);
            sql.Append("order by numbered.[Handle] ");
            sql.Append(sort);

            using (var conn = new SqlConnection(_connectionString)) {
                conn.Open();
                using (var cmd = new SqlCommand(sql.ToString(), conn)) {
                    foreach (var param in parameters)
                        cmd.Parameters.Add(param);

                    using (var reader = cmd.ExecuteReader())
                        return ReadHeaders(reader);
                }
            }
        }

        public ICollection<ExceptionHeader> GetHeaders(IEnumerable<string> handles) {
            if (handles == null)
                throw new ArgumentNullException("handles");

            var sql = new StringBuilder("select [Handle], [Name], [Value] from [ExceptionProperty] where [Handle] in ");

            RenderHandles(handles, sql);

            using (var conn = new SqlConnection(_connectionString)) {
                conn.Open();
                using (var cmd = new SqlCommand(sql.ToString(), conn)) {
                    using (var reader = cmd.ExecuteReader())
                        return ReadHeaders(reader);
                }
            }
        }

        public string GetContent(string handle) {
            if (string.IsNullOrEmpty(handle))
                throw new ArgumentException();

            long handleValue;
            if (!SqlExceptionWriter.TryParseHandle(handle, out handleValue))
                throw new ArgumentException();

            var sql = "select top 1 [Content] from [ExceptionContent] where [Handle] = " + handleValue.ToString();

            using (var conn = new SqlConnection(_connectionString)) {
                conn.Open();
                using (var cmd = new SqlCommand(sql, conn))
                using (var reader = cmd.ExecuteReader())
                    if (reader.Read())
                        return reader.GetString(0);
            }

            return null;
        }

        public int Delete(ExceptionQuery query) {
            if (query == null)
                throw new ArgumentNullException("query");

            var parameters = new List<SqlParameter>();
            var sql = new StringBuilder("delete [ExceptionContent] where [Handle] in (select c.[Handle]\r\n");
            RenderFromSection(query, sql, parameters);
            sql.Append(")");

            using (var conn = new SqlConnection(_connectionString)) {
                conn.Open();
                using (var cmd = new SqlCommand(sql.ToString(), conn)) {
                    foreach (var param in parameters)
                        cmd.Parameters.Add(param);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public int Delete(IEnumerable<string> handles) {
            if (handles == null)
                throw new ArgumentNullException("handles");

            var sql = new StringBuilder("delete [ExceptionContent] where [Handle] in ");

            RenderHandles(handles, sql);

            using (var conn = new SqlConnection(_connectionString)) {
                conn.Open();
                using (var cmd = new SqlCommand(sql.ToString(), conn))
                    return cmd.ExecuteNonQuery();
            }
        }

        static void RenderFromSection(ExceptionQuery query, StringBuilder sql, ICollection<SqlParameter> parameters) {
            sql.AppendLine("from [ExceptionContent] c");

            if (query.Filter.RequiredProperties.Count > 0)
                sql.AppendLine("join [ExceptionProperty] h on c.[Handle] = h.[Handle]");

            if (query.WriteUtcFrom.HasValue || query.WriteUtcTo.HasValue || query.Filter.RequiredProperties.Count > 0) {
                sql.Append("where ");
                RenderWhereSection(query, sql, parameters);
            }
        }

        static void RenderWhereSection(ExceptionQuery query, StringBuilder sql, ICollection<SqlParameter> parameters) {
            var empty = true;
            string p;

            if (query.WriteUtcFrom.HasValue) {
                if (!empty)
                    sql.Append("and ");
                empty = false;

                sql.Append("c.[WriteUtc] >= ");
                sql.AppendLine(p = "@p" + parameters.Count.ToString());
                parameters.Add(new SqlParameter(p, query.WriteUtcFrom.Value));
            }

            if (query.WriteUtcTo.HasValue) {
                if (!empty)
                    sql.Append("and ");
                empty = false;

                sql.Append("c.[WriteUtc] <= ");
                sql.AppendLine(p = "@p" + parameters.Count.ToString());
                parameters.Add(new SqlParameter(p, query.WriteUtcTo.Value));
            }

            if (query.Filter.RequiredProperties.Count > 0) {
                if (!empty)
                    sql.Append("and ");
                empty = false;

                sql.AppendLine("(");
                var firstProperty = true;
                foreach (var property in query.Filter.RequiredProperties) {
                    sql.Append("\t");

                    if (!firstProperty)
                        sql.Append("or ");
                    firstProperty = false;

                    sql.Append("(h.[Name] = ");
                    sql.Append(p = "@p" + parameters.Count.ToString());
                    parameters.Add(new SqlParameter(p, property.PropertyName));

                    if (property.AllowedValues.Count == 1) {
                        sql.Append(" and h.[Value] = ");
                        sql.Append(p = "@p" + parameters.Count.ToString());
                        parameters.Add(new SqlParameter(p, property.AllowedValues[0]));
                    }
                    else if (property.AllowedValues.Count > 1) {
                        sql.Append(" and h.[Value] in (");
                        var firstValue = true;
                        foreach (var value in property.AllowedValues) {
                            if (!firstValue)
                                sql.Append(",");
                            firstValue = false;

                            sql.Append(p = "@p" + parameters.Count.ToString());
                            parameters.Add(new SqlParameter(p, value));
                        }
                        sql.Append(")");
                    }

                    sql.AppendLine(")");
                }
                sql.AppendLine(")");
            }
        }

        static void RenderHandles(IEnumerable<string> handles, StringBuilder sql) {
            var empty = true;

            sql.Append("(");

            foreach (var handle in handles) {
                if (!empty)
                    sql.Append(",");

                long handleValue;
                if (!SqlExceptionWriter.TryParseHandle(handle, out handleValue))
                    throw new ArgumentOutOfRangeException("handles");

                sql.Append(handleValue);
                empty = false;
            }

            if (empty)
                throw new ArgumentOutOfRangeException("handles");

            sql.Append(")");
        }

        static ICollection<ExceptionHeader> ReadHeaders(IDataReader reader) {
            var result = new List<ExceptionHeader>();
            ExceptionHeader currentHeader = null;
            long currentHandle = 0;

            while (reader.Read()) {
                var handle = reader.GetInt64(0);
                var name = reader.GetString(1);
                var value = reader.IsDBNull(2) ? null : reader.GetString(2);

                if (currentHeader == null || currentHandle != handle) {
                    if (currentHeader != null)
                        result.Add(currentHeader);
                    currentHeader = new ExceptionHeader();
                    currentHeader.Properties[ExceptionProperty.Handle] = SqlExceptionWriter.GetHandle(currentHandle = handle);
                }

                currentHeader.Properties.Add(name, value);
            }

            if (currentHeader != null)
                result.Add(currentHeader);

            return result;
        }
    }
}